Forum Discussion
Solved
Combining IF and INDEX functions
I'm trying to find a formula that will allow me to display multiple fields of data when a specific value (a product SKU) is entered into a cell.
I managed to get this to work with a VLOOKUP for...
Hi Peter,
As a variant I may suggest to form your data in Excel Tables (available from Excel 2007). To convert the range which has table structure (i.e. headers and rows with data) just stay somewhere within the range and press Ctrl+T.
For your sample tables looks like
In LookupTable1 in first row in Brand column enter the formula
=VLOOKUP(LookupTable1[@[SKU]:[SKU]],ReferenceTable,MATCH(LookupTable1[[#Headers],[Brand]],ReferenceTable[#Headers],0),FALSE)
First part
LookupTable1[@[SKU]:[SKU]]
is kind of absolute reference which fixes your SKU column when you copy the formula to the right.
MATCH returns the index of the column in ReferenceTable which has the same header as in LookupTable.
When drag the formula to the right with Copy cells (not copy/paste) till end of your table.
Similar for the second lookup table.
That's all. No need to copy/paste and/or change your formulas when you expand your Reference table. Adding SKU into lookup table you only need to enter SKU into the next from the bottom of the lookup table line. The table will auto expands and formulas in next columns auto-fills returning you values from the Reference table.
As variant INDEX/MATCH could be used, see LookupTable3 and LookupTable4. Same result, only the formula above looks like
=INDEX(ReferenceTable,MATCH(LookupTable3[@[SKU]:[SKU]],ReferenceTable[[SKU]:[SKU]],0),MATCH(LookupTable3[[#Headers],[Brand]],ReferenceTable[#Headers],0))
Please see attached file with the sample.
Hi Sergei, thank you for responding. I did look into INDEX/MATCH formulas but I don't think it does quite what I need it to. From what I can see, that formula doesn't seem to allow for a look-up function, unless I'm doing it wrong. I'll look into it again. Thank you.
Hi Peter,
Yes, with workbook it'll be easier. Just a guess, perhaps instead of IF(P15=D:D, in your formula it shall be
=IF(IFNA(MATCH(P15,D:D,0),0),...
Hi Sergei,
I've attached an example workbook which hopefully illustrates what I'm trying to achieve.
My Reference Table contains a list of products along with associated costs, all indexed with an SKU.
Lookup Table 1 contains a VLOOKUP formula in the top row that allows me to enter an SKU and then display the corresponding data from the Reference Table.
That essentially does what I want it to do, with the following limitiations:
1) If I add any new rows in the Reference Table when I buy in more products then it will break my VLOOKUP formula because the Table_array will change and the formula doesn't adapt.
2) For Lookup Table 2, I only want to display select columns from the Reference Table (it will be for distribution to retailers and I don't want them to see my Net Costs).
3) The VLOOKUP formula obviously uses absolute references so that when I drag the formula horizontally to copy it into other columns it retains the Lookup_value and Table_array references. However, I'm unable to copy the working formula into the rows below as it just takes with it the values already in the cells.
So, what I need is a formula that will allow me to perform a look-up function that will adapt when I make changes to the Reference Table, and allow me to choose which columns to draw data from and display. I also need to work out how to copy that formula effectively into subsequent rows.
I've read that INDEX is better than VLOOKUP as the formula won't break when amending the reference Table_array, but I can only work out how to make INDEX return data in one cell when I specify what to look for, which is effectively a ='cell' formula.
I don't think I need a MATCH formula because I not asking it to search for various matches, I simply need to be able to enter a product code in one cell, and display corresponding data from the row associated with that product code.
I hope that makes sense!
Hi Peter,
As a variant I may suggest to form your data in Excel Tables (available from Excel 2007). To convert the range which has table structure (i.e. headers and rows with data) just stay somewhere within the range and press Ctrl+T.
For your sample tables looks like
In LookupTable1 in first row in Brand column enter the formula
=VLOOKUP(LookupTable1[@[SKU]:[SKU]],ReferenceTable,MATCH(LookupTable1[[#Headers],[Brand]],ReferenceTable[#Headers],0),FALSE)
First part
LookupTable1[@[SKU]:[SKU]]
is kind of absolute reference which fixes your SKU column when you copy the formula to the right.
MATCH returns the index of the column in ReferenceTable which has the same header as in LookupTable.
When drag the formula to the right with Copy cells (not copy/paste) till end of your table.
Similar for the second lookup table.
That's all. No need to copy/paste and/or change your formulas when you expand your Reference table. Adding SKU into lookup table you only need to enter SKU into the next from the bottom of the lookup table line. The table will auto expands and formulas in next columns auto-fills returning you values from the Reference table.
As variant INDEX/MATCH could be used, see LookupTable3 and LookupTable4. Same result, only the formula above looks like
=INDEX(ReferenceTable,MATCH(LookupTable3[@[SKU]:[SKU]],ReferenceTable[[SKU]:[SKU]],0),MATCH(LookupTable3[[#Headers],[Brand]],ReferenceTable[#Headers],0))
Please see attached file with the sample.
- Sergei, you are a genius!
It took me a while to get it to work as I've not used Excel Tables before and didn't realise right away that I had to name the table in the settings as well. But it now all works and does exactly what I want it to do, and adapts if I add rows into my source table as well.
This is going to make my life so much easier in future when dealing with stock lists. I can't thank you enough for your help, it really is appreciated.
