Is this a VLOOKUP function?

Originally Posted by AliGW

I think attaching a sample file showing your data might help here.

It would also help if you manually enter the results you're expecting into that sample file to aid your explanation.

Thanks for fast replies. I have added a sample xlsm file to the OP.

Originally Posted by AliGW

I don't understand at all - sorry! Can you try to explain in detail how you arrive at that answer?

So if a row has the same K to O values that the row with the equation on, we want it to copy the value in column AQ to the cell with the equation.

In this example: Row 14 has the same values in columns K to O as row 2, so it should copy the value in cell AQ14 to cell P2 (the cell with the equation in). The value in AQ14 is 2555518HDHHTXL

Originally Posted by AliGW

Is this table constantly growing? If so, do new entries get added at the top or the bottom?

Yeah, it does. We currently have over 12,000 rows on our main database. Do you know if there's a way to limit the number of values it pulls into the cell also?

Originally Posted by jason.b75

One observation, the value, 103 in cell N14 is formatted as text, which cannot be directly compared to the value, 103 in cell N2, which is formatted as a number. You need the format to be consistent for this to work.

Once you have corrected the format of N14, enter this formula into P2, and confirm it as an array formula.

=IFERROR(INDEX($AQ$2:$AQ$15,MATCH(2,1/(($K$2:$K$15=K2)*($L$2:$L$15=L2)*($M$2:$M$15=M2)*($N$2:$N$15=N2)*(ROW($K$2:$K$15)<>ROW(K2))))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

edit:-

That might struggle to cope with 12,000 rows, maybe a helper column is needed.

Thanks for the help

Hi All,

Happy to interact with you all though this forum.

My VLOOKUP is not working Yes / No options
I have given the formula like the below to check Yes / no options.



=IF(D6="No","",IF(F6<>"",VLOOKUP(F6,'Compliance Base'!$G$1:$H$19,2),""))

For yes it is working properly but while " NO" it is returning #N/A.

I am also attaching screenshot to have immediate response.

Please let me know what I am missing here.
Appreciate your help.

Regards,
JJ

srijaa,

Please take a moment to read the forum rules, once you have familiarised yourself with them please start your own thread.

http://www.excelforum.com/forum-rule...rum-rules.html

Originally Posted by jason.b75

One observation, the value, 103 in cell N14 is formatted as text, which cannot be directly compared to the value, 103 in cell N2, which is formatted as a number. You need the format to be consistent for this to work.

Once you have corrected the format of N14, enter this formula into P2, and confirm it as an array formula.

=IFERROR(INDEX($AQ$2:$AQ$15,MATCH(2,1/(($K$2:$K$15=K2)*($L$2:$L$15=L2)*($M$2:$M$15=M2)*($N$2:$N$15=N2)*(ROW($K$2:$K$15)<>ROW(K2))))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

edit:-

That might struggle to cope with 12,000 rows, maybe a helper column is needed.

Jason, do you know if there is a way of it pulling in 4 results separated by a “|” character?

There is a way, but I wouldn't recommend it. The formula was before you mentioned 12,000 rows of data, which I think it will struggle with, if you want to pull 4 answers per row, then you're effectively increasing it to 48,000 rows. Maybe time to borrow one of NASA's supercomputers.

Before we start looking at more efficient methods, one thing that will probably need allowing for. On the assumption that there could be more than 4 entries which match in all 4 columns, how do we decide which 4 should be shown?

Say for example, you wanted the results based on the 4 highest values in column H (I chose that column because all of the entries in the sample have the same value), what should we use as tiebreak criteria (if critical).