Concatenate within table_array in Vlookup

[madcat1981]

Hi all,

I'm hoping this is just a quick and easy thing I'm missing but I cannot seem to figure out how to use a concatination within the table_array of a Vlookup.

So far I have

=IF(ISNA(VLOOKUP(A2&H2&I2,Billed!AE:AE,1,FALSE)),"Not Billed","Billed")

What I am trying to do is replace the "Billed!AE:AE" part with "Billed!A2&H2&I2" but I am unsure if this is correct and if so what does the col_index_num become?

Thanks in advanced.

Guy

 

[CharlesChuckieCharles]

=IF(ISNA(VLOOKUP(A2&H2&I2,Billed!AE:AE,1,FALSE)),"Not Billed","Billed")

so A2&H2&I2 is the concatenation of three cells

to produce a value that

may or may not be in Billed!AE:AE

if it is you return "Not Billed"

if not you return "Billed"

What do you want?

Billed!AE:AE" part with "Billed!A2&H2&I2 what do you actually want to see

 

[madcat1981]

Thank you for your reply.

Basically I have two sheets, one has booked sales information and the other billed. Using information in columns A, H and I (which is the same on each sheet) I create a combined string in each and then within the booked sheet check to see if it exists within the billed sheet. This then tells me if an order line has billed.

Using my current formula it creates the combined reference within the booked information for me then matches this against the still combined one within the billed. If it finds a match it comes back with "Billed" or if #N/A says that it is "Not Billed".

What I would like to do is instead of using a column with the combined reference in the billed sheet is do it within the formula hence changing "Billed!AE:AE" part with "Billed!A2&H2&I2".

I am unsure if this A) is possible or correct and B) if it is, what does the col_index_num value have to be.

Hopefully that helps.

Guy

 

[CharlesChuckieCharles]

oops I got the "Billed Not Billed" bit backwards (forgot the ISNA)

Concatenating cells by A2&B2... etc works fine to build the lookup key watch out for leading and trailing spaces in all the key segments and the lookup key column


You are only looking at one column AE so the column index has to be 1 (which will return the key field if there is a match or #N/A if not)

So using A2&H2.. on the current sheet to lookup Billed!AE:AE

It all looks like it should work?

 

[madcat1981]

What I'm trying to do is avoid having the column AE with the concatenated ref in the billed sheet. So what happens is the code does the concatination like it does with the lookup_value.

Therefore there won't be Billed!AE:AE within the vlookup for the table_array, instead it will the concatenated Billed!A2&H2&I2.

This obviously takes away the current col_index_num of 1.

Guy

 

[CharlesChuckieCharles]

No you can't do the lookup column.. not with VLOOKUP

INDEX and MATCH maybe but I'm not the expert

try reposting asking about INDEX and match

 

[madcat1981]

Hmm, I think I may run into the same problems of concatinating the lookup array.

Guy