Question on Table Array in VLOOKUP function

[w_k_c]

Hi All,

I am trying to use vlookup function, but my table array is a bit tricky. For example, my table array is B:E and col_index_num is 4 (i.e. return value in column E). However, my column A contains some specific numbers, e.g 161 and 171.

Is there any way/formula to only lookup the value that column contains 161?

Thanks a lot!

[sktneer]

Hi w_k_c,
Welcome to the Forum.

If you are trying to return the first match found in loopup table array, you may try this array formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter. (though index match can also be used for this)

Please Login or Register  to view this content.

Where C1 contains the lookup value, B2:E100 is table array and A2:A100 contains your additional criteria (161).

But if there are multiple matches and you want to return all of them, you will need a different formula. If this is not what you are trying to achieve, attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[azumi]

You mean only 161 ?

=VLOOKUP(161,B2:E4,4,FALSE)

[humdingaling]

Please Login or Register  to view this content.

CSE Formula (control+ Shift + Enter)

F4 = 161
G4 = whatever you looking up in column B

[w_k_c]

Thanks All. It seems working. Appreciated for your help!