Forum Discussion
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 formula, whereby I could enter the SKU into one cell, and have the following cells display various values, such as the brand, item, Net cost, VAT, gross price and so on.
However, want to use an INDEX formula instead so that if I edit the reference table then it won't affect the returned results. I also want to be able to specify which columns/value are returned (for example, I don't necessarily want to display the Net cost of a product.
From what I can tell, VLOOKUP only allows for either a single column's value to be returned, or all of the specified columns from left to right, with no option to omit any.
I think I need a combination of IF and INDEX, whereby IF the value matches an SKU in, say, Column A, then values relating to that product code are returned.
I thought I was getting there with the following formula (albeit only being able to return one value):
=IF((P15=D:D),INDEX($E$15:$O$105,0,2)).
D is the worksheet column in which all of the SKUs are listed.
This worked if I entered the relevant SKU on row 15, as it returned the correct gross price that was displayed in column 2 of the reference table.
However, a value is only returned if the SKU is that of row 15, otherwise it displays 'FALSE' if I try and search prices using any other any other SKU input.
This has been driving me mad for hours, so I'd be hugely grateful if anyone is able to enlighten me.
Thank you.
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 Peter,
I guess you don't need IF here, use INDEX/MATCH combination (e.g. https://exceljet.net/formula/basic-index-match-exact)
- 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),...
Peter,
could you provide an anonymized workbook?
- Hi Detlef, thanks for your response. Yes I'll post an example workbook in a couple of days, I'm away skiing at the moment!
