SOLVED

Combining IF and INDEX functions

Copper Contributor

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.

8 Replies

Hi Peter,

 

I guess you don't need IF here, use INDEX/MATCH combination (e.g. https://exceljet.net/formula/basic-index-match-exact) 

Peter,

 

could you provide an anonymized workbook?

 

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 Detlef, thanks for your response. Yes I'll post an example workbook in a couple of days, I'm away skiing at the moment!

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!

best response confirmed by Peter Fleckney (Copper Contributor)
Solution

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

LookupTables.JPG

 

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.
1 best response

Accepted Solutions
best response confirmed by Peter Fleckney (Copper Contributor)
Solution

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

LookupTables.JPG

 

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.

 

View solution in original post