Lookup Right & Below i.e Horizontal & Vertical lookup to filter data

[paradise2sr]

Hi,

I am trying to use

Please Login or Register  to view this content.

which is nothing but a horizontal & vertical lookup but there is problem with the 2nd AND criterial.However the 1st criteria works verify fine.

This Filter function is one go function i.e it should spill the data using one single function.

[TMS]

See if this helps:

Please Login or Register  to view this content.

Bit more complex than your filter with two conditions. Have yet to work out how to remove the header row.

Anyway, something to play with.

[HansDouwe]

I'm missing the expected results, so here's a guess.

This formula selects the rows where sheet2!A3:A9<>"" with the columns where sheet2!B2:I2=B1:

Please Login or Register  to view this content.

Is this what you want?

[Glenn Kennedy]

or ... yet another!

=LET(d,Sheet2!C3:I9,r,Sheet2!B3:B9,c,Sheet2!C2:I2,cr,Sheet2!A3:A9,fr,FILTER(r,cr<>""),rfr,SEQUENCE(ROWS(fr)),fd,FILTER(FILTER(d,c=$B$1),cr<>""),s,SEQUENCE(,COLUMNS(fd)+1),IF(s<2,fr,INDEX(fd,rfr,s-1)))

[HansDouwe]

If you also want to return the first column with letters, as in Glenn's result in post #4, then this is an alternative formula:

Please Login or Register  to view this content.

But now we are actually guessing what result you are looking for.

[TMS]

HansDouwe's solution removing the zeroes.

Please Login or Register  to view this content.

[HansDouwe]

@TMS, No I didn't remove the zeroes. I formatted the zero's invisible, because it concerns empty fields in the original file.
If you change the format of the cells to Number or Standard the zeroes are back again.

[TMS]

It doesn't remove zeroes where zeroes were present, nor does it remove decimal places. It removes zeroes where the source was blank.

[HansDouwe]

@TMS. Sorry I don't understand you. My solution does not move zeroes where corresponding field are empty. The zeroes are there but invisible. If you change the format to number for example. The zeroes are visible again.

[paradise2sr]

Thanx all of u for your kind support.It had worked.

[Glenn Kennedy]

Hahaha. I did overcomplicate that one a bit...

[HansDouwe]

Thx for the feedback. I.m glad the given solutions work.

Would you please consider adding reputation to the helpers answers that you think deserve.